using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using eShop.Model;
using eShop.Utility;

namespace eShop.DataAccess
{
    public class ProductImageDAO : DatabaseUtility
    {
        public static Int32 CreateNewTblProductAdditionalImage
        (
            Int32 productID,
            String additionalImageUrl
        )
        {
            //creating an empty TblProductAdditionalImage object
            ProductImage tblProductAdditionalImage = new ProductImage();
            tblProductAdditionalImage.ProductID = productID;
            tblProductAdditionalImage.AdditionalImageUrl = additionalImageUrl;

            return ProductImageDAO.CreateNewTblProductAdditionalImage(tblProductAdditionalImage);
        }

        public static bool UpdateTblProductAdditionalImage
        (
            Int32 iD,
            Int32 productID,
            String additionalImageUrl
        )
        {
            ProductImage tblProductAdditionalImage = ProductImageDAO.GetTblProductAdditionalImageByID(iD);

            tblProductAdditionalImage.ProductID = productID;
            tblProductAdditionalImage.AdditionalImageUrl = additionalImageUrl;

            return ProductImageDAO.UpdateTblProductAdditionalImage(tblProductAdditionalImage);
        }

        public static bool DeleteTblProductAdditionalImage
        (
            Int32 iD
        )
        {
            //Validate Input
            if (iD <= GetIDMinValue)
                throw (new ArgumentOutOfRangeException("iD"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@ID", SqlDbType.Int, 0, ParameterDirection.Input, iD);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTADDITIONALIMAGE_DELETETBLPRODUCTADDITIONALIMAGE);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? false : true);

        }

        public static bool DeleteTblProductAdditionalImageList(System.Collections.Generic.List<Int32> iDListToDelete)
        {
            //Validate Input
            foreach (Int32 iD in iDListToDelete)
                if (iD <= GetIDMinValue)
                    throw (new ArgumentOutOfRangeException("iD"));

            //performing deletion operation //

            string xmlString = FormatXMLForIdArray(iDListToDelete);

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@IDList", SqlDbType.Xml, xmlString.ToString().Length, ParameterDirection.Input, xmlString.ToString());
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTADDITIONALIMAGE_DELETETBLPRODUCTADDITIONALIMAGELIST);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == iDListToDelete.Count ? true : false);
        }

        public static ProductImage GetTblProductAdditionalImageByID(Int32 iD)
        {
            if (iD <= GetIDMinValue)
                throw (new ArgumentOutOfRangeException("iD"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ID", SqlDbType.Int, 0, ParameterDirection.Input, iD);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTADDITIONALIMAGE_GETTBLPRODUCTADDITIONALIMAGEBYID);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductAdditionalImageCollectionFromReader);
            List<ProductImage> objCollection = ((List<ProductImage>)ExecuteReaderCmd(sqlCmd, test));

            if (objCollection.Count > 0)
                return objCollection[0];
            else
                return null;
        }

        public static List<ProductImage> GetTblProductAdditionalImageByProductID(Int32 productID)
        {
            if (productID <= GetProductIDMinValue)
                throw (new ArgumentOutOfRangeException("productID"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ProductID", SqlDbType.Int, 0, ParameterDirection.Input, productID);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTADDITIONALIMAGE_GETTBLPRODUCTADDITIONALIMAGEBYPRODUCTID);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductAdditionalImageCollectionFromReader);
            List<ProductImage> objCollection = ((List<ProductImage>)ExecuteReaderCmd(sqlCmd, test));

            if (objCollection.Count > 0)
                return objCollection;
            else
                return null;
        }

        public static List<ProductImage> GetTblProductAdditionalImageByProductIDPaged(Int32 productID, String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            //Validate Input
            if (productID <= GetProductIDMinValue)
                throw (new ArgumentOutOfRangeException("productID"));

            if (orderBy == "")
                orderBy = "ProductID";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ProductID", SqlDbType.Int, 0, ParameterDirection.Input, productID);
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRowIndex);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, maximumRows);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTADDITIONALIMAGE_GETTBLPRODUCTADDITIONALIMAGEBYPRODUCTIDPAGED);

            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductAdditionalImageCollectionFromReader);
            List<ProductImage> objCollection = ((List<ProductImage>)ExecuteReaderCmd(sqlCmd, test));

            return objCollection;
        }

        public static int GetTblProductAdditionalImageByProductIDPagedCount(Int32 productID, String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            //Validate Input
            if (productID <= GetProductIDMinValue)
                throw (new ArgumentOutOfRangeException("productID"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ProductID", SqlDbType.Int, 0, ParameterDirection.Input, productID);
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTADDITIONALIMAGE_GETTBLPRODUCTADDITIONALIMAGEBYPRODUCTIDPAGEDCOUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static List<ProductImage> GetAllTblProductAdditionalImage()
        {

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTADDITIONALIMAGE_GETALLTBLPRODUCTADDITIONALIMAGE);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductAdditionalImageCollectionFromReader);
            List<ProductImage> objCollection = ((List<ProductImage>)ExecuteReaderCmd(sqlCmd, test));

            return objCollection;
        }

        public static List<ProductImage> GetAllTblProductAdditionalImagePaged(String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            //Validate Input
            if (orderBy == "")
                orderBy = "ID";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRowIndex);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, maximumRows);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTADDITIONALIMAGE_GETALLTBLPRODUCTADDITIONALIMAGEPAGED);

            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductAdditionalImageCollectionFromReader);
            List<ProductImage> objCollection = ((List<ProductImage>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetAllTblProductAdditionalImagePagedCount(String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTADDITIONALIMAGE_GETALLTBLPRODUCTADDITIONALIMAGEPAGEDCOUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static bool Save(ProductImage productImage)
        {
            if (productImage.ID <= GetIDMinValue)
            {
                Int32 TempId = CreateNewTblProductAdditionalImage(productImage);
                if (TempId > 0)
                {
                    productImage.ID = TempId;
                    return true;
                }
                else
                {
                    // error occurs
                    return false;
                }
            }
            else
            {	//This is a update Command
                return (UpdateTblProductAdditionalImage(productImage));
            }

        }

        public static Int32 CreateNewTblProductAdditionalImage(ProductImage newTblProductAdditionalImage)
        {
            // validate Parameters
            if (newTblProductAdditionalImage == null)
                throw (new ArgumentNullException("newTblProductAdditionalImage"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            int length = 0;
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParameterToSqlCmd(sqlCmd, "@ProductID", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductAdditionalImage.ProductID);
            AddParameterToSqlCmd(sqlCmd, "@AdditionalImageUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProductAdditionalImage.AdditionalImageUrl);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTADDITIONALIMAGE_CREATENEWTBLPRODUCTADDITIONALIMAGE);
            ExecuteScalarCmd(sqlCmd);
            return (Int32.Parse(sqlCmd.Parameters["@ReturnValue"].Value.ToString()));

        }

        public static bool UpdateTblProductAdditionalImage(ProductImage newTblProductAdditionalImage)
        {
            // validate Parameters
            if (newTblProductAdditionalImage == null)
                throw (new ArgumentNullException("newTblProductAdditionalImage"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            int length = 0;
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParameterToSqlCmd(sqlCmd, "@ID", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductAdditionalImage.ID);
            AddParameterToSqlCmd(sqlCmd, "@ProductID", SqlDbType.Int, 0, ParameterDirection.Input, newTblProductAdditionalImage.ProductID);
            AddParameterToSqlCmd(sqlCmd, "@AdditionalImageUrl", SqlDbType.NVarChar, 500, ParameterDirection.Input, newTblProductAdditionalImage.AdditionalImageUrl);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPRODUCTADDITIONALIMAGE_UPDATETBLPRODUCTADDITIONALIMAGE);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? false : true);
        }

        public static IList GenerateTblProductAdditionalImageCollectionFromReader(IDataReader returnData)
        {

            //creating the instance of Employee collection
            List<ProductImage> colTblProductAdditionalImage = new List<ProductImage>();
            while (returnData.Read())
            {
                //passing the TblProductAdditionalImage constructor parameters from the current instance of data reader fields.
                ProductImage newTblProductAdditionalImage = new ProductImage()
                {
                    ID = returnData["ID"] == System.DBNull.Value ? GetIDMinValue : (Int32)returnData["ID"],
                    ProductID = returnData["ProductID"] == System.DBNull.Value ? 0 : (Int32)returnData["ProductID"],
                    AdditionalImageUrl = returnData["AdditionalImageUrl"] == System.DBNull.Value ? string.Empty : (String)returnData["AdditionalImageUrl"]

                };
                //adding the Employee to the collection
                colTblProductAdditionalImage.Add(newTblProductAdditionalImage);
            }

            //returns the collection of Companys 
            return (colTblProductAdditionalImage);
        }

        public static Int32 GetIDMinValue
        {
            get
            {
                return 0;
            }
        }
        public static Int32 GetProductIDMinValue
        {
            get
            {
                return 0;
            }
        }

        //create method related contants
        internal const string SPR_TBLPRODUCTADDITIONALIMAGE_CREATENEWTBLPRODUCTADDITIONALIMAGE = "spr_TblProductAdditionalImage_CreateNewTblProductAdditionalImage";
        internal const string SPR_TBLPRODUCTADDITIONALIMAGE_UPDATETBLPRODUCTADDITIONALIMAGE = "spr_TblProductAdditionalImage_UpdateTblProductAdditionalImage";
        internal const string SPR_TBLPRODUCTADDITIONALIMAGE_DELETETBLPRODUCTADDITIONALIMAGE = "spr_TblProductAdditionalImage_DeleteTblProductAdditionalImage";
        internal const string SPR_TBLPRODUCTADDITIONALIMAGE_DELETETBLPRODUCTADDITIONALIMAGELIST = "spr_TblProductAdditionalImage_DeleteTblProductAdditionalImageList";
        internal const string SPR_TBLPRODUCTADDITIONALIMAGE_GETTBLPRODUCTADDITIONALIMAGEBYID = "spr_TblProductAdditionalImage_GetTblProductAdditionalImageByID";
        internal const string SPR_TBLPRODUCTADDITIONALIMAGE_GETTBLPRODUCTADDITIONALIMAGEBYPRODUCTID = "spr_TblProductAdditionalImage_GetTblProductAdditionalImageByProductID";
        internal const string SPR_TBLPRODUCTADDITIONALIMAGE_GETTBLPRODUCTADDITIONALIMAGEBYPRODUCTIDPAGED = "spr_TblProductAdditionalImage_GetTblProductAdditionalImageByProductIDPaged";
        internal const string SPR_TBLPRODUCTADDITIONALIMAGE_GETTBLPRODUCTADDITIONALIMAGEBYPRODUCTIDPAGEDCOUNT = "spr_TblProductAdditionalImage_GetTblProductAdditionalImageByProductIDPagedCount";
        internal const string SPR_TBLPRODUCTADDITIONALIMAGE_GETALLTBLPRODUCTADDITIONALIMAGE = "spr_TblProductAdditionalImage_GetAllTblProductAdditionalImage";
        internal const string SPR_TBLPRODUCTADDITIONALIMAGE_GETALLTBLPRODUCTADDITIONALIMAGEPAGED = "spr_TblProductAdditionalImage_GetAllTblProductAdditionalImagePaged";
        internal const string SPR_TBLPRODUCTADDITIONALIMAGE_GETALLTBLPRODUCTADDITIONALIMAGEPAGEDCOUNT = "spr_TblProductAdditionalImage_GetAllTblProductAdditionalImagePagedCount";
    }
}
